package org.csu.jpetstore.persistence.impl;

import org.csu.jpetstore.domain.Order;
import org.csu.jpetstore.persistence.DBUtil;
import org.csu.jpetstore.persistence.OrderDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class OrderDAOImpl implements OrderDAO {

    private static final String INSERT_ORDER = "INSERT INTO ORDERS (orderid, USERID, ORDERDATE, SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE,\n" +
            "      SHIPZIP, SHIPCOUNTRY, BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY,\n" +
            "      COURIER, TOTALPRICE, BILLTOFIRSTNAME, BILLTOLASTNAME, SHIPTOFIRSTNAME, SHIPTOLASTNAME,\n" +
            "      CREDITCARD, EXPRDATE, CARDTYPE)\n" +
            "    VALUES(?, ?, ?, ?, ?, ?,\n" +
            "      ?, ?, ?, ?, ?, ?,\n" +
            "      ?, ?, ?, ?, ?, ?,\n" +
            "      ?, ?, ?, ?, ?, ?)";

    private static final String GET_ORDER_BY_USERNAME = "SELECT\n" +
            "      BILLADDR1 AS billAddress1,\n" +
            "      BILLADDR2 AS billAddress2,\n" +
            "      BILLCITY,\n" +
            "      BILLCOUNTRY,\n" +
            "      BILLSTATE,\n" +
            "      BILLTOFIRSTNAME,\n" +
            "      BILLTOLASTNAME,\n" +
            "      BILLZIP,\n" +
            "      SHIPADDR1 AS shipAddress1,\n" +
            "      SHIPADDR2 AS shipAddress2,\n" +
            "      SHIPCITY,\n" +
            "      SHIPCOUNTRY,\n" +
            "      SHIPSTATE,\n" +
            "      SHIPTOFIRSTNAME,\n" +
            "      SHIPTOLASTNAME,\n" +
            "      SHIPZIP,\n" +
            "      CARDTYPE,\n" +
            "      COURIER,\n" +
            "      CREDITCARD,\n" +
            "      EXPRDATE AS expiryDate,\n" +
            "      LOCALE,\n" +
            "      ORDERDATE,\n" +
            "      ORDERS.ORDERID,\n" +
            "      TOTALPRICE,\n" +
            "      USERID AS username\n" +
            "    FROM ORDERS\n" +
            "    WHERE ORDERS.USERID = ?\n" +
            "    ORDER BY ORDERDATE";

    private static final String GET_ORDER_BY_ID = "select\n" +
            "      BILLADDR1 AS billAddress1,\n" +
            "      BILLADDR2 AS billAddress2,\n" +
            "      BILLCITY,\n" +
            "      BILLCOUNTRY,\n" +
            "      BILLSTATE,\n" +
            "      BILLTOFIRSTNAME,\n" +
            "      BILLTOLASTNAME,\n" +
            "      BILLZIP,\n" +
            "      SHIPADDR1 AS shipAddress1,\n" +
            "      SHIPADDR2 AS shipAddress2,\n" +
            "      SHIPCITY,\n" +
            "      SHIPCOUNTRY,\n" +
            "      SHIPSTATE,\n" +
            "      SHIPTOFIRSTNAME,\n" +
            "      SHIPTOLASTNAME,\n" +
            "      SHIPZIP,\n" +
            "      CARDTYPE,\n" +
            "      COURIER,\n" +
            "      CREDITCARD,\n" +
            "      EXPRDATE AS expiryDate,\n" +
            "      LOCALE,\n" +
            "      ORDERDATE,\n" +
            "      ORDERS.ORDERID,\n" +
            "      TOTALPRICE,\n" +
            "      USERID AS username\n" +
//            "      STATUS\n" +
//            "    FROM ORDERS, ORDERSTATUS\n" +
//            "    WHERE ORDERS.ORDERID = ?\n" +
//            "      AND ORDERS.ORDERID = ORDERSTATUS.ORDERID";
            "    FROM ORDERS\n" +
            "    WHERE ORDERS.ORDERID = ?";


    public static final String SELECT_ALL_ID = "SELECT orderid From orders";

    @Override
    public int insertOrder(Order order) {
        int result = 0;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ORDER);

            preparedStatement.setInt(1,order.getOrderId());
            preparedStatement.setString(2,order.getUsername());
            preparedStatement.setString(3,order.getOrderDate().toString());
            preparedStatement.setString(4,order.getShipAddress1());
            preparedStatement.setString(5,order.getShipAddress2());
            preparedStatement.setString(6,order.getShipCity());
            preparedStatement.setString(7,order.getShipState());
            preparedStatement.setString(8,order.getShipZip());
            preparedStatement.setString(9,order.getShipCountry());
            preparedStatement.setString(10,order.getBillAddress1());
            preparedStatement.setString(11,order.getBillAddress2());
            preparedStatement.setString(12,order.getBillCity());
            preparedStatement.setString(13,order.getBillState());
            preparedStatement.setString(14,order.getBillZip());
            preparedStatement.setString(15,order.getBillCountry());
            preparedStatement.setString(16,order.getCourier());
            preparedStatement.setBigDecimal(17,order.getTotalPrice());
            preparedStatement.setString(18,order.getBillToFirstName());
            preparedStatement.setString(19,order.getBillToLastName());
            preparedStatement.setString(20,order.getShipToFirstName());
            preparedStatement.setString(21,order.getShipToLastName());
            preparedStatement.setString(22,order.getCreditCard());
            preparedStatement.setString(23,order.getExpiryDate());
            preparedStatement.setString(24,order.getCardType());

            result=preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return result;
    }

    @Override
    public Order getOrderByOrderid(int orderid) {
        Order order = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ORDER_BY_ID);
            preparedStatement.setInt(1,orderid);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()) {
                order = new Order();
                order.setBillAddress1(resultSet.getString(1));
                order.setBillAddress2(resultSet.getString(2));
                order.setBillCity(resultSet.getString(3));
                order.setBillCountry(resultSet.getString(4));
                order.setBillState(resultSet.getString(5));
                order.setBillToFirstName(resultSet.getString(6));
                order.setBillToLastName(resultSet.getString(7));
                order.setBillZip(resultSet.getString(8));
                order.setShipAddress1(resultSet.getString(9));
                order.setShipAddress2(resultSet.getString(10));
                order.setShipCity(resultSet.getString(11));
                order.setShipCountry(resultSet.getString(12));
                order.setShipState(resultSet.getString(13));
                order.setShipToFirstName(resultSet.getString(14));
                order.setShipToLastName(resultSet.getString(15));
                order.setShipZip(resultSet.getString(16));
                order.setCardType(resultSet.getString(17));
                order.setCourier(resultSet.getString(18));
                order.setCreditCard(resultSet.getString(19));
                order.setExpiryDate(resultSet.getString(20));
                order.setLocale(resultSet.getString(21));
                order.setOrderDate(new Date(resultSet.getString(22)));
                order.setOrderId(resultSet.getInt(23));
                order.setTotalPrice(resultSet.getBigDecimal(24));
                order.setUsername(resultSet.getString(25));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return order;
    }

    @Override
    public List<Order> getOrderByUsername(String username) {
        List<Order> orderList = new ArrayList<Order>();
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ORDER_BY_USERNAME);
            preparedStatement.setString(1,username);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                Order order = new Order();
                order.setBillAddress1(resultSet.getString(1));
                order.setBillAddress2(resultSet.getString(2));
                order.setBillCity(resultSet.getString(3));
                order.setBillCountry(resultSet.getString(4));
                order.setBillState(resultSet.getString(5));
                order.setBillToFirstName(resultSet.getString(6));
                order.setBillToLastName(resultSet.getString(7));
                order.setBillZip(resultSet.getString(8));
                order.setShipAddress1(resultSet.getString(9));
                order.setShipAddress2(resultSet.getString(10));
                order.setShipCity(resultSet.getString(11));
                order.setShipCountry(resultSet.getString(12));
                order.setShipState(resultSet.getString(13));
                order.setShipToFirstName(resultSet.getString(14));
                order.setShipToLastName(resultSet.getString(15));
                order.setShipZip(resultSet.getString(16));
                order.setCardType(resultSet.getString(17));
                order.setCourier(resultSet.getString(18));
                order.setCreditCard(resultSet.getString(19));
                order.setExpiryDate(resultSet.getString(20));
                order.setLocale(resultSet.getString(21));

                java.util.Date date = new java.util.Date(resultSet.getString(22));
                order.setOrderDate(date);

                order.setOrderId(resultSet.getInt(23));
                order.setTotalPrice(resultSet.getBigDecimal(24));
                order.setUsername(resultSet.getString(25));
                orderList.add(order);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return orderList;
    }

    @Override
    public List<Integer> getAllOrderId() {
        List<Integer> list = new ArrayList<Integer>();
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_ID);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                int i = resultSet.getInt(1);
                list.add(i);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

}
